package com.bst.common.utils.sql;

import com.bst.common.enums.DataSourceTypeEnum;
import com.bst.common.exception.UtilException;
import com.bst.common.utils.HdpDateUtil;
import com.bst.common.utils.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * sql操作工具类
 * 
 * @author ruoyi
 */
public class SqlUtil
{
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "\u000B|and |extractvalue|updatexml|sleep|exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |or |union |like |+|/*|user()";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 限制orderBy最大长度
     */
    private static final int ORDER_BY_MAX_LENGTH = 500;

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value)
    {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value))
        {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        if (StringUtils.length(value) > ORDER_BY_MAX_LENGTH)
        {
            throw new UtilException("参数已超过最大限制，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value)
    {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value)
    {
        if (StringUtils.isEmpty(value))
        {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords)
        {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1)
            {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    public static String buildSql(String sql, String dayBegin, String dayEnd, String dbType) {
        try {
            Date startTime = HdpDateUtil.FORMAT_YYYY_MM_DD.parse(dayBegin);
            Date endTime = HdpDateUtil.FORMAT_YYYY_MM_DD.parse(dayEnd);
            return buildSql(sql,startTime,endTime,dbType);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public static String buildSql(String sql, Date startTime, Date endTime, String dbType) {
        Map<String, String> map = getGlobalParam(startTime, endTime, dbType);
        for (Map.Entry<String, String> entry : map.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            while (sql.indexOf(key) != -1)
                sql = sql.replace(key, value);
        }
        return sql;
    }

    private final static String[] weekDays = {"星期一", "星期二", "星期三", "星期四", "星期五", "星期六","星期日"};

    public final static SimpleDateFormat FORMAT_HHMMSS = new SimpleDateFormat("HH:mm:ss");
    public final static SimpleDateFormat FORMAT_YYYY_MM_DD_HHMMSS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    public final static SimpleDateFormat FORMAT_YYYYMMDD = new SimpleDateFormat("yyyyMMdd");
    public final static SimpleDateFormat FORMAT_YYYY_MM_DD = new SimpleDateFormat("yyyy-MM-dd");
    public final static SimpleDateFormat FORMAT_YYYY_MM = new SimpleDateFormat("yyyy-MM");

    public static final String DT_BEGIN_KEY = "${DT_BEGIN}";//date类型的日期的开始
    public static final String DT_DAY_BEGIN_INT_KEY = "${DT_DAY_BEGIN_INT}";//date类型的日期的开始
    public static final String D_BEGIN_KEY = "${D_BEGIN}";//字符串类型的日期开始
    public static final String DT_END_KEY = "${DT_END}";//date类型的日期的开始
    public static final String DT_DAY_END_INT_KEY = "${DT_DAY_END_INT}";//date类型的日期的开始
    public static final String D_END_KEY = "${D_END}";//字符串类型的日期结束
    public static final String D_BEGIN_KEY_ = "${D_BEGIN2}";//字符串类型的日期开始
    public static final String D_END_KEY_ = "${D_END2}";//字符串类型的结束日期
    public static Map<String, String> getGlobalParam(Date start, Date end, String dbType) {
        Map<String, String> map = new LinkedHashMap<>();
        String beginDayStr = HdpDateUtil.toDateStrByFormat(start, "yyyy-MM-dd HH:mm:ss");
        String endDayStr = HdpDateUtil.toDateStrByFormat(end, "yyyy-MM-dd HH:mm:ss");
        String dayBegin = HdpDateUtil.toDateStrByFormat(start, "yyyy-MM-dd");
        String dayEnd = HdpDateUtil.toDateStrByFormat(end, "yyyy-MM-dd");
        dbType = dbType.toUpperCase();
        if (dbType.equals(DataSourceTypeEnum.ORACLE)) {
            map.put(DT_BEGIN_KEY, "to_date ('" + dayBegin + "','yyyy-mm-dd')");
            map.put(DT_END_KEY, "to_date ('" + dayEnd + "','yyyy-mm-dd')");
        } else {
            map.put(DT_BEGIN_KEY, "'" + beginDayStr + "'");
            map.put(DT_END_KEY, "'" + endDayStr + "'");
        }
        map.put(D_BEGIN_KEY, "'" + dayBegin + "'");
        map.put(DT_DAY_BEGIN_INT_KEY, dayBegin);
        map.put(D_END_KEY, "'" + dayEnd + "'");
        map.put(DT_DAY_END_INT_KEY, dayEnd);
        map.put(D_BEGIN_KEY_, "'" + dayBegin + "'");
        map.put(D_END_KEY_, "'" + dayEnd + "'");
        return map;
    }
}
